﻿SET QUOTED_IDENTIFIER ON
GO
SET ARITHABORT ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE [dbo].[bhp_NemoGroupWithFilesGetAll]
WITH ENCRYPTION
AS
BEGIN

	SELECT DISTINCT
		tg.id
	FROM
		n_Tree tc
		INNER JOIN n_Tree tg ON tc.parent = tg.id
	WHERE
		tc.typ = 'I'
		AND tc.del = 0
		AND tg.del = 0

END
GO
CREATE PROCEDURE [dbo].[bhp_NemoFilesInfoGetAll]
WITH ENCRYPTION
AS
BEGIN

	DECLARE
		@GroupId	INT,
		@GroupName	NVARCHAR(200),
		@ColumnId	INT,
		@ColumnName	NVARCHAR(200),
		@Sql		NVARCHAR(MAX),
		@AddUnion	BIT

	SET @Sql = N''
	SET @AddUnion = 0

	DECLARE Structure CURSOR LOCAL FAST_FORWARD FOR
		SELECT DISTINCT
			tg.id,
			tg.name,
			tc.id,
			tc.name
		FROM
			n_Tree tc
			INNER JOIN n_Tree tg ON tc.parent = tg.id
		WHERE
			tc.typ = 'I'
			AND tc.del = 0
			AND tg.del = 0

	OPEN Structure
	WHILE 1=1
	BEGIN
		FETCH NEXT FROM Structure INTO @GroupId, @GroupName, @ColumnId, @ColumnName
		IF @@FETCH_STATUS <> 0 BREAK;

		IF @AddUnion = 1
			SET @Sql = @Sql + ' UNION ';

		SET @Sql = N'SELECT 
						' + CAST(@GroupId as NVARCHAR(10)) + ' as GroupId, 
						''' + @GroupName + ''' as GroupName, 
						' + CAST(@ColumnId as NVARCHAR(10)) + ' as ColumnId, 
						''' + @ColumnName + ''' as ColumnName, 
						idx as RecordId,
						' + @ColumnName + '_name as [FileName],
						' + @ColumnName + '_hash as [FileGuid],
						CONCAT(mtime, ctime) as LastModified
					FROM 
						' + @GroupName + ' 
					WHERE 
						del = 0
						AND ' + @ColumnName + '_data IS NOT NULL'

		SET @AddUnion = 1

	END
	CLOSE Structure

	EXEC sp_executesql @Sql

END
